<?php
App::uses('AppModel', 'Model');
/**
 * User Model
 */
class Schedule extends AppModel {


	public $useDbConfig = 'axiaCon';
	
	public $useTable = false;


	//VALIDATION
	
	function jobList($userid)
    {

        $jobList = "select j.JobName, r.JobId, rs.RequestId, rs.RequestScheduleId, rs.ScheduleDate, cast(rs.VisitStartTime as time) [VisitStartTime], cast(rs.VisitEndTime as time) [VisitEndTime], cast(rs.EstimatedStartTime as time) [EstimatedStartTime], cast(rs.EstimatedEndTime as time) [EstimatedEndTime]
					from app_RequestSchedule rs, app_RequestScheduleStaff rss, app_Request r, app_Job j
					WHERE j.JobId = r.JobId
					AND r.RequestId = rs.RequestId
					AND rs.RequestScheduleId = rss.RequestScheduleId 
					AND rss.UserId = $userid
					AND rs.ScheduleDate >= DATEADD(day,-1,GETDATE())
					AND rs.ScheduleDate <= DATEADD(day,7,GETDATE())
					ORDER BY ScheduleDate, rs.VisitStartTime ASC";

        return $this->query($jobList);
    }
	
	function allJob()
    {

        $jobList = "select j.JobName, r.JobId, rs.RequestId, rs.RequestScheduleId, rs.ScheduleDate, cast(rs.VisitStartTime as time) [VisitStartTime], cast(rs.VisitEndTime as time) [VisitEndTime], cast(rs.EstimatedStartTime as time) [EstimatedStartTime], cast(rs.EstimatedEndTime as time) [EstimatedEndTime]
					from app_RequestSchedule rs, app_RequestScheduleStaff rss, app_Request r, app_Job j
					WHERE j.JobId = r.JobId
					AND r.RequestId = rs.RequestId
					AND rs.RequestScheduleId = rss.RequestScheduleId 
					AND rs.ScheduleDate >= DATEADD(day,-1,GETDATE())
					AND rs.ScheduleDate <= DATEADD(day,7,GETDATE())
					ORDER BY ScheduleDate, rs.VisitStartTime ASC";

        return $this->query($jobList);
    }
	
	function allJobList()
    {

        $allJobList = "select j.JobName, r.JobId, rs.RequestId, rs.RequestScheduleId, rs.ScheduleDate, cast(rs.VisitStartTime as time) [VisitStartTime], cast(rs.VisitEndTime as time) [VisitEndTime], cast(rs.EstimatedStartTime as time) [EstimatedStartTime], cast(rs.EstimatedEndTime as time) [EstimatedEndTime]
					from app_RequestSchedule rs, app_RequestScheduleStaff rss, app_Request r, app_Job j
					WHERE j.JobId = r.JobId
					AND r.RequestId = rs.RequestId
					AND rs.RequestScheduleId = rss.RequestScheduleId 
					AND rs.ScheduleDate >= DATEADD(day,-1,GETDATE())
					AND rs.ScheduleDate <= DATEADD(day,7,GETDATE())
					ORDER BY ScheduleDate, rs.VisitStartTime ASC";
        return $this->query($allJobList);
    }
	
	function verifyUserJob($userid, $jobid, $requestid, $requestscheduleid)
	{
		$userCheck = "SELECT  r.JobId, rs.RequestId, rs.RequestScheduleId, rss.UserId
						FROM app_RequestScheduleStaff rss, app_RequestSchedule rs, app_Request r
						WHERE r.RequestId = rs.RequestId
						AND rs.RequestScheduleId = rss.RequestScheduleId
						AND rss.UserId = $userid
						AND r.JobId = $jobid
						AND rs.RequestId = $requestid
						AND rss.RequestScheduleId = $requestscheduleid";
		return $this->query($userCheck);
	}
	
	function getJobRequest($jobid, $requestid)
	{
		$getJob = "select j.JobId, j.JobName, j.JobType, 
					r.RequestId, r.RequestVersion, r.RequestStatus, r.Description, r.StartDate, r.EndDate
					from app_Job j, app_Request r
					WHERE j.JobId = r.JobId
					AND j.JobId = $jobid
					AND r.RequestId = $requestid";
		return $this->query($getJob);
	}
	
	function getClients($requestid)
	{
		$getClient = "select c.ClientId, c.FirstName, c.Surname, c.GenderPreference, c.DOB, c.RequiresRFCarSeatBefore, c.RequiresFFCarSeatBefore, c.RequiresBoosterSeatBefore
						from app_RequestClient rc, app_client c
						WHERE rc.RequestId = $requestid
						AND rc.ClientId = c.ClientId
						ORDER BY c.FirstName";
		return $this->query($getClient);
	}
	
	function getRouteContact($requestid, $uid)
	{
		$getRoute = "select rrps.RoutePointSequenceNo, rrps.RoutePointSequence, rrps.RouteName, rrps.ContactLocationFlag, rrps.ExpectedArrivalTime, rrps.ExpectedDuration, rrps.ExpectedDepartureTime, rrps.Note,
						a.Latitude, a.Longitude, a.AddressName, a.LevelNumber, a.UnitNumber, a.StreetNumber, a.StreetName, a.StreetSuffix, a.TownSuburb, a.State, a.Country, a.PostCode,
						u.UserId, u.FirstName, u.Surname
						from app_RequestRoutePointStaff rrps, app_Address a, cor_User u
						where rrps.RequestId = $requestid
						AND u.UserId = $uid
						AND u.UserId = rrps.UserId
						AND rrps.AddressId = a.AddressId
						ORDER BY rrps.UserId, rrps.RoutePointSequenceNo
						";
		return $this->query($getRoute);
	}
	
	function getRouteContactAdmin($requestid)
	{
		$getRoute = "select rrps.RoutePointSequenceNo, rrps.RoutePointSequence, rrps.RouteName, rrps.ContactLocationFlag, rrps.ExpectedArrivalTime, rrps.ExpectedDuration, rrps.ExpectedDepartureTime, rrps.Note,
						a.AddressName, a.LevelNumber, a.UnitNumber, a.StreetNumber, a.StreetName, a.StreetSuffix, a.TownSuburb, a.State, a.Country, a.PostCode,
						u.UserId, u.FirstName, u.Surname
						from app_RequestRoutePointStaff rrps, app_Address a, cor_User u
						where rrps.RequestId = $requestid
						AND u.UserId = rrps.UserId
						AND rrps.AddressId = a.AddressId
						ORDER BY rrps.UserId, rrps.RoutePointSequenceNo
						";
		return $this->query($getRoute);
	}
	function getRouteStaffAdmin($requestid)
	{
		$getStaffOnRoute = "select u.UserId, u.FirstName, u.Surname
							from app_RequestRoutePointStaff rrps, cor_User u
							where rrps.RequestId = $requestid
							AND u.UserId = rrps.UserId
							GROUP BY u.UserId, u.FirstName, u.Surname
							ORDER BY u.UserId
							";
		return $this->query($getStaffOnRoute);
	}
	function getRouteStaff($requestid, $uid)
	{
		$getStaffOnRoute = "select u.UserId, u.FirstName, u.Surname
							from app_RequestRoutePointStaff rrps, cor_User u
							where rrps.RequestId = $requestid
							AND u.UserId = rrps.UserId
							AND u.UserId = $uid
							GROUP BY u.UserId, u.FirstName, u.Surname
							ORDER BY u.UserId
							";
		return $this->query($getStaffOnRoute);
	}
	
	function getRouteAdmin($requestid)
	{
		$getRoute = "select rrs.RequestRouteStaffId, rrs.FromRequestRoutePointId, rrs.ToRequestRoutePointId, rrs.FromRoutePointSequence, rrs.ToRoutePointSequence, rrs.Distance, rrs.EstimatedDuration,
						rrp.AddressId, rrp.RoutePointSequence, rrp.RouteName, rrp.ContactLocationFlag, rrp.ExpectedArrivalTime, rrp.ExpectedDepartureTime, rrp.Note,
						a.LevelNumber, a.UnitNumber, a.StreetNumber, a.StreetName, a.StreetSuffix, a.TownSuburb, a.State, a.Country, a.PostCode,
						rrp.UserId
						from app_RequestRouteStaff rrs, app_RequestRoutePointStaff rrp, app_Address a
						where rrs.RequestId = $requestid
						AND rrs.UserId = rrp.UserId
						AND a.AddressId = rrp.AddressId
						AND rrp.RequestRoutePointId = rrs.FromRequestRoutePointId
						UNION
						select rrs.RequestRouteStaffId, rrs.FromRequestRoutePointId, rrs.ToRequestRoutePointId, rrs.FromRoutePointSequence, rrs.ToRoutePointSequence, rrs.Distance, rrs.EstimatedDuration,
						rrp.AddressId, rrp.RoutePointSequence, rrp.RouteName, rrp.ContactLocationFlag, rrp.ExpectedArrivalTime, rrp.ExpectedDepartureTime, rrp.Note,
						a.LevelNumber, a.UnitNumber, a.StreetNumber, a.StreetName, a.StreetSuffix, a.TownSuburb, a.State, a.Country, a.PostCode,
						rrp.UserId
						from app_RequestRouteStaff rrs, app_RequestRoutePointStaff rrp,  app_Address a
						where rrs.RequestId = $requestid
						AND rrs.UserId = rrp.UserId
						AND a.AddressId = rrp.AddressId
						AND rrp.RequestRoutePointId = rrs.ToRequestRoutePointId
						ORDER BY rrp.UserId, rrp.ExpectedArrivalTime ASC
						";
		return $this->query($getRoute);
	}
			
	function getNotes($requestid)
	{
		$getNote = "select Importance, NoteText, CreatedOn from app_Note WHERE EntityId = $requestid ORDER BY CreatedOn DESC";
		return $this->query($getNote);
	}
	//The Associations below have been created with all possible keys, those that are not needed can be removed

/**
 * belongsTo associations
 *
 * @var array
 */

}
